package com.lyc.dao.imp;

import java.sql.SQLException;
import java.util.List;

import com.lyc.dao.CountryInfoDAO;
import com.lyc.dto.CountDto;
import com.lyc.entity.CountryInfo;
import com.lyc.util.DBUtil;

/**
 * @Program: medal
 * @Author: YC
 * @Date: 2022/5/5 17:40
 */

public class CountryInfoDAOImpl extends DBUtil.BaseDAOImpl<CountryInfo> implements CountryInfoDAO {
	@Override
	public List<CountDto> countMedal() throws SQLException {
		String sql = "SELECT\n" + " tb1.*,\n" + " IFNULL(tb2.num1,0) as num1,\n" + " IFNULL(tb2.num2,0) as num2,\n"
				+ " IFNULL(tb2.num3,0) as num3,\n" + " IFNULL(tb2.total,0) as total\n" + "FROM country_info tb1\n"
				+ "LEFT JOIN\n" + "(\n" + "\tSELECT \n" + "\t\tcountry_code,\n"
				+ "\t\tcount(if(medal_num=1,1,null)) as num1,\n" + "\t\tcount(if(medal_num=2,1,null)) as num2,\n"
				+ "\t\tcount(if(medal_num=3,1,null)) as num3,\n" + "\t\tcount(*) as total\n" + "\tfrom (\n"
				+ "\tSELECT DISTINCT b.country_code,se_id,a.medal_num from medal_info a\n"
				+ "\tINNER JOIN sportsman_info b\n" + "\tON a.sportsman_id=b.sportsman_id\n"
				+ "\t)c GROUP BY country_code\n" + ") tb2\n"
				+ "ON tb1.country_code=tb2.country_code order BY num1 desc, num2 desc,num3 desc\n";
		return DBUtil.selectList(sql, CountDto.class);
	}
}
